package com.beyeasy.tvremind;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;


public class DBAdapter
{
	private static final String DB_NAME = "tvremind.db";
	private static final int DB_VERSION = 1;

	private static final String DB_TAB_FUND_COM = "S_FUND_COMPANY_T";
	public static final String KEY_COM_ID = "COM_ID";
	public static final String KEY_COM_NAME_SHORT = "COM_NAME_SHORT";
	public static final String KEY_COM_NAME = "COM_NAME";
	public static final String KEY_COM_URL = "COM_URL";
	public static final String KEY_COM_TEL = "COM_TEL";

	public class FundCom
	{
		public String ComID;
		public String ComNameShort;
		public String ComName;
		public String ComURL;
		public String ComTel;
	}

	private static SQLiteDatabase db;
	private static Context context;
	private DBOpenHelper dbOpenHelper;

	private static class DBOpenHelper extends SQLiteOpenHelper
	{
		//private static String SQLCreateTableFundCom;
		//private static String SQLCreateTableFundName;
		//private static String SQLCreateTableFundValue;

		private static String SQLCreateTableFundCom	=
			"CREATE TABLE S_FUND_COMPANY_T(" +
			"COM_ID VARCHAR(8) NOT NULL, " +
			"COM_NAME VARCHAR(100) NOT NULL, " +
			"COM_NAME_SHORT VARCHAR(40) NOT NULL, " +
			"COM_URL VARCHAR(200), " +
			"COM_TEL VARCHAR(20));";

		private static String SQLCreateTableFundName =
			"CREATE TABLE S_FUND_NAME_T(" +
			"FUND_ID VARCHAR(6) NOT NULL," +
			"FUND_NAME VARCHAR(100) NOT NULL," +
			"FUND_COM_ID VARCHAR(8)," +
			"FUND_TYPE_ID VARCHAR(3));";

		private static String SQLCreateTableFundValue =
			"CREATE TABLE F_FUND_VALUE_T(" +
			"FUND_ID VARCHAR(6) NOT NULL," +
			"VALUE_DATE VARCHAR(14) NOT NULL," +
			"VALUE REAL," +
			"VALUE_PERCENT REAL);";

		public DBOpenHelper(Context context, String name, CursorFactory factory, int version)
		{
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase _db)
		{
			//SQLCreateTableFundCom = context.getString(R.string.SQLCreateTableFundCom);
			//SQLCreateTableFundName = context.getString(R.string.SQLCreateTableFundName);
			//SQLCreateTableFundValue = context.getString(R.string.SQLCreateTableFundValue);

			_db.execSQL(SQLCreateTableFundCom);
			_db.execSQL(SQLCreateTableFundName);
			_db.execSQL(SQLCreateTableFundValue);
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion)
		{
		}
	}

	public DBAdapter(Context _context)
	{
		context = _context;
	}

	public void open() throws SQLiteException
	{
		dbOpenHelper = new DBOpenHelper(context, DB_NAME, null, DB_VERSION);
		try
		{
			db = dbOpenHelper.getWritableDatabase();
		}
		catch (SQLiteException ex)
		{
			db = dbOpenHelper.getReadableDatabase();
		}
	}

    public void close()
    {
        if (db != null)
        {
        	db.close();
        	db = null;
        }
    }

    public boolean initDb()
    {
    	try
    	{
    		InputStream in = context.getAssets().open("fund.sql");
    		BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(in));
    		String sqlUpdate = null;
    		while ((sqlUpdate = bufferedReader.readLine()) != null)
    		{
    			if (!TextUtils.isEmpty(sqlUpdate))
    			{
    				db.execSQL(sqlUpdate);
    			}
    		}
    		bufferedReader.close();
    		in.close();
    	}
    	catch (SQLException e)
    	{
    		Log.v("", e.getMessage());
    		return false;
    	}
    	catch (IOException e)
    	{
    		Log.v("", e.getMessage());
    		return false;
    	}
    	return true;
    }


    public int getFundComCount()
    {
		Cursor results = db.query(DB_TAB_FUND_COM,
				new String[]{KEY_COM_ID, KEY_COM_NAME_SHORT, KEY_COM_NAME, KEY_COM_URL, KEY_COM_TEL},
				null, null, null, null, null, null);
		return results.getCount();
    }

    public boolean initFundCom()
    {
    	return true;
    }

	public long insertFundCom(FundCom fundCom)
	{
		ContentValues newFundCom = new ContentValues();
		newFundCom.put(KEY_COM_ID, fundCom.ComID);
		newFundCom.put(KEY_COM_NAME_SHORT, fundCom.ComNameShort);
		newFundCom.put(KEY_COM_NAME, fundCom.ComName);
		newFundCom.put(KEY_COM_URL, fundCom.ComURL);
		newFundCom.put(KEY_COM_TEL, fundCom.ComTel);
		return db.insert(DB_TAB_FUND_COM, null, newFundCom);
	}

	public long deleteFundComAll()
	{
		return db.delete(DB_TAB_FUND_COM, null, null);
	}

	public long deleteFundCom(long id)
	{
		return db.delete(DB_TAB_FUND_COM, KEY_COM_ID + "=" + id, null);
	}

	public FundCom[] queryFundComAll()
	{
		Cursor results = db.query(DB_TAB_FUND_COM,
				new String[]{KEY_COM_ID, KEY_COM_NAME_SHORT, KEY_COM_NAME, KEY_COM_URL, KEY_COM_TEL},
				null, null, null, null, null, null);
		return ConvertToFundCom(results);
	}

	public FundCom[] queryFundCom(long id)
	{
		Cursor results = db.query(DB_TAB_FUND_COM,
				new String[]{
				KEY_COM_ID, KEY_COM_NAME_SHORT, KEY_COM_NAME, KEY_COM_URL, KEY_COM_TEL},
				KEY_COM_ID + "=" + id, null, null, null, null, null);
		return ConvertToFundCom(results);

	}

	public long updateOneData(long id, FundCom fundCom)
	{
		ContentValues updateFundCom = new ContentValues();
		updateFundCom.put(KEY_COM_NAME_SHORT, fundCom.ComNameShort);
		updateFundCom.put(KEY_COM_NAME, fundCom.ComName);
		updateFundCom.put(KEY_COM_URL, fundCom.ComURL);
		updateFundCom.put(KEY_COM_TEL, fundCom.ComTel);

		return db.update(DB_TAB_FUND_COM, updateFundCom,  KEY_COM_ID + "=" + id, null);
	}

	private FundCom[] ConvertToFundCom(Cursor cursor)
	{
		int resultCounts = cursor.getCount();
		if (resultCounts == 0 || !cursor.moveToFirst())
		{
			return null;
		}

		FundCom[] fundComs = new FundCom[resultCounts];

		for (int i = 0; i < resultCounts; i++)
		{
			fundComs[i] = new FundCom();
			fundComs[i].ComID = cursor.getString(cursor.getColumnIndex(KEY_COM_ID));
			fundComs[i].ComNameShort = cursor.getString(cursor.getColumnIndex(KEY_COM_NAME_SHORT));
			fundComs[i].ComName = cursor.getString(cursor.getColumnIndex(KEY_COM_NAME));
			fundComs[i].ComURL = cursor.getString(cursor.getColumnIndex(KEY_COM_URL));
			fundComs[i].ComTel = cursor.getString(cursor.getColumnIndex(KEY_COM_TEL));
			cursor.moveToNext();
		}
		return fundComs;

	}

}

